﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Odbc;
using Org.SqlHelper;

namespace QualityRpt.Dao.Db2
{
    public class DeviceSpecDao:IDeviceSpecDao
    {
        const string cachePrefix = "DSL-";
        public DataRow Save(DataRow row)
        {
            object rt = null;
            using (OdbcConnection conn = ODBCHelper.GetConnection())
            {
                ODBCHelper.ExecuteNonQuery(conn, CommandType.Text, "INSERT INTO RPT.DEVICE_SPEC(SUPPLIER_ID,DEVICE_ID,SPEC_NAME,SPEC_SHORTNAME) VALUES(?,?,?,?)",
                    ODBCHelper.CreateInputParameter("SUPPLIER_ID", OdbcType.VarChar, 20, row["SUPPLIER_ID"].ToString()),
                    ODBCHelper.CreateInputParameter("DEVICE_ID", OdbcType.VarChar, 20, row["DEVICE_ID"].ToString()),
                    ODBCHelper.CreateInputParameter("SPEC_NAME", OdbcType.VarChar, 20, row["SPEC_NAME"].ToString()),
                    ODBCHelper.CreateInputParameter("SPEC_SHORTNAME", OdbcType.VarChar, 20, row["SPEC_SHORTNAME"].ToString()));
                rt = ODBCHelper.ExecuteScalar(conn, CommandType.Text, "SELECT IDENTITY_VAL_LOCAL() FROM SYSIBM.SYSDUMMY1");
            }
            CacheManager.RemoveByPrefix(cachePrefix);
            if (rt != null)
            {
                row["ID"] = Convert.ToInt32(rt);
                return row;
            }
            return null;
        }

        public DataRow Update(DataRow row)
        {
            using (OdbcConnection conn = ODBCHelper.GetConnection())
            {
                ODBCHelper.ExecuteNonQuery(conn, CommandType.Text, "UPDATE RPT.DEVICE_SPEC SET SUPPLIER_ID=?,DEVICE_ID=?,SPEC_NAME=?,SPEC_SHORTNAME=? WHERE ID=?",
                    ODBCHelper.CreateInputParameter("SUPPLIER_ID", OdbcType.VarChar, 20, row["SUPPLIER_ID"].ToString()),
                    ODBCHelper.CreateInputParameter("DEVICE_ID", OdbcType.VarChar, 20, row["DEVICE_ID"].ToString()),
                    ODBCHelper.CreateInputParameter("SPEC_NAME", OdbcType.VarChar, 20, row["SPEC_NAME"].ToString()),
                    ODBCHelper.CreateInputParameter("SPEC_SHORTNAME", OdbcType.VarChar, 20, row["SPEC_SHORTNAME"].ToString()),
                    ODBCHelper.CreateInputParameter("ID", OdbcType.Int, Convert.ToInt32(row["ID"])));
            }
            CacheManager.RemoveByPrefix(cachePrefix);
            return row;
        }

        public DataRow Get(long id)
        {
            DataTable rtTable = GetList("and ID="+id);
            if (rtTable.Rows.Count > 0)
            {
                return rtTable.Rows[0];
            }
            return null;
        }

        public void Delete(long id)
        {
            using (OdbcConnection conn = ODBCHelper.GetConnection())
            {
                ODBCHelper.ExecuteNonQuery(conn, CommandType.Text, "DELETE FROM RPT.DEVICE_SPEC WHERE ID=?",
                    ODBCHelper.CreateInputParameter("ID", OdbcType.Int, id));
            }
            CacheManager.RemoveByPrefix(cachePrefix);
        }

        public DataTable GetList(string where)
        {
            return GetList(where, "ID ASC");
        }

        public DataTable GetList(string where, string sort)
        {
            DataTable rtTable = null;
            StringBuilder sbText = new StringBuilder();
            sbText.Append("SELECT * FROM RPT.DEVICE_SPEC ");
            if (where.Trim() != string.Empty)
            {
                sbText.Append(" WHERE " + where);
            }
            if (sort.Trim() != string.Empty)
            {
                sbText.Append(" ORDER BY " + sort);
            }
            string cacheKey = cachePrefix + sbText.ToString();
            if (CacheManager.Get(cacheKey) == null)
            {
                using (OdbcConnection conn = ODBCHelper.GetConnection())
                {
                    rtTable = ODBCHelper.ReadTable(conn, CommandType.Text, sbText.ToString());
                }
                CacheManager.Put(cacheKey, rtTable);
            }
            else
            {
                rtTable = CacheManager.Get(cacheKey) as DataTable;
            }
            return rtTable;
        }

        public DataTable GetPageList(Utils.PageUtils pu, string where, string sort)
        {
            throw new NotImplementedException();
        }
        public DataTable GetBySpIn(string spIn, int devID)
        {
            StringBuilder sbSql = new StringBuilder();
            if (spIn!=null && spIn.Trim()!=string.Empty)
            {
                sbSql.Append(" SUPPLIER_ID IN (" + spIn+")");
            }
            if (devID > 0)
            {
                if (sbSql.ToString() != string.Empty)
                {
                    sbSql.Append(" AND ");
                }
                sbSql.Append(" DEVICE_ID = " + devID);
            }
            return GetList(sbSql.ToString());
        }
        public DataRow GetByName(string name)
        {
            DataRow rtRow = null;
            DataTable table = GetList("SPEC_NAME='"+name+"'");
            if (table.Rows.Count > 0)
            {
                rtRow = table.Rows[0];
            }
            return rtRow;
        }
    }
}
